package js;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.io.RandomAccessFile;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;

import util.AES;
import util.CSVUtils;
import util.ConfigUtils;
import util.ConnectionManager;
import util.DbUtils;


public class CsvExport {
	private static Logger logger=LogManager.getLogger(CsvExport.class);

	static Date d = new Date();
	static SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddhhmmss");
	static String dateNowStr = sdf.format(d);

	private static final String File_PATH = ConfigUtils.getInstance().getValue(
			"localbasePath");// �ļ����մ��Ŀ¼
	private static final String File_NAME = ConfigUtils.getInstance().getValue(
			"filename");// �ļ����մ��Ŀ¼
	private static final String OK_PATH = ConfigUtils.getInstance().getValue(
			"localbaseOkPath");//ok�ļ����Ŀ¼
	private static final String EXPORT_FLAG = ConfigUtils.getInstance().getValue(
            "exportFlag");//��������

	/**
	 * ����csv
	 * @Description:
	 * @author liurr
	 * @date 2018��5��3�� ����3:26:41
	 * @param args
	 * @throws Exception
	 */
	public static void main(String[] args) throws Exception {
		long startTime=System.currentTimeMillis();
		logger.info("************��ʼ�����ݿ����ӳ�************");
		ConnectionManager.getInstance();
		if("sevenDayAlreadySend".equals(EXPORT_FLAG)) {
        	his();
        } else {
        	logger.info("************��ȡ��վ��Ϣ************");
    		CsvExport csvExport=new CsvExport();
    		List<Map<String,Object>> list=csvExport.findBaseStationInfo();
    		logger.info("************����csv�ļ�************");
    		AssembledData(list);
    		logger.info("************����ok�ļ���ֵ************");
    		writeTxtFile(File_NAME);
		}
		long endTime=System.currentTimeMillis();
		logger.info("*******�ܺ�ʱ" + (endTime - startTime) / 100+"��*****************");
	}

	/**
	 * ��ȡ��վ��Ϣ
	 * @Description:
	 * @author liurr
	 * @date 2018��5��3�� ����3:32:38
	 * @return
	 * @throws Exception
	 */
	public List<Map<String,Object>> findBaseStationInfo() throws Exception{
		int count = 0;
		Connection conn = null;
//        PreparedStatement pstmt = null;
		Statement stmt = null;
		ResultSet rs = null;
		List<Map<String,Object>> list=new ArrayList<Map<String,Object>>();
		String sql =  "select t.phone_number,  "
				+"t.station_id, "
				+"     t.city_id, "
				+"      to_char(t.enter_time, 'yyyy/mm/dd hh24:mi:ss'), "
				+"     to_char(t.receive_time, 'yyyy/mm/dd hh24:mi:ss'), "
				+"     t.user_place, "
				+"     t.user_city, "
				+"     t.Is_Local, "
				+"     t.is_cz, "
				+"     to_char(t.operator_time, 'yyyy/mm/dd hh24:mi:ss') "
				+"  from his_base_target_users t where to_char(t.operator_time, 'yyyy/mm/dd') = to_char(sysdate-1, 'yyyy/mm/dd') ";

		try {
			conn = ConnectionManager.getInstance().getConnection();
//            pstmt = conn.prepareStatement(sql);
//            rs = pstmt.executeQuery();
			stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
					ResultSet.CONCUR_READ_ONLY);
			stmt.setFetchSize(300);
			rs = stmt.executeQuery(sql);
			ResultSetMetaData md = rs.getMetaData(); //��ý�����ṹ��Ϣ,Ԫ����
			int columnCount = md.getColumnCount();   //�������
			while (rs.next()) {
				count++;
				Map<String,Object> rowData = new LinkedHashMap<String,Object>();
				for (int i = 1; i <= columnCount; i++) {
					rowData.put(md.getColumnName(i), rs.getObject(i));
				}
				list.add(rowData);
				if (count %10000 ==0) {
					logger.info("get data ##"+count);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (null != rs) {
					rs.close();
					rs = null;
				}
				if (null != stmt) {
					stmt.close();
				}
			} catch (SQLException e) {

				e.printStackTrace();
			}
			DbUtils.closeQuietly(conn);
		}
		return list;
	}

	/**
	 * ��װ����
	 * @Description:
	 * @author liurr
	 * @date 2018��5��3�� ����3:25:57
	 * @param retList
	 */
	public static void AssembledData(List<Map<String,Object>> retList) {
		if(retList==null||retList.size()==0){
			logger.info("************���ݿ��վ��ϢΪ��************");
		}else{
			List<Object> head=new ArrayList<Object>();
			List<List<Object>> dataList=new ArrayList<List<Object>>();
			for (Map<String, Object> map : retList) {
				List<Object> date=new ArrayList<Object>();
				for(Object key:map.keySet()){
					date.add(map.get(key));
				}
				dataList.add(date);
			}
			for(Object ob:retList.get(0).keySet()){
				head.add(ob);
			}
			CSVUtils.createCSVFile(head,dataList,File_PATH,File_NAME);
		}

	}
	/**
	 * д�ļ�
	 * @Description:
	 * @author liurr
	 * @date 2018��8��21�� ����4:36:08
	 * @param newStr
	 * @throws IOException
	 */
	public static void writeTxtFile(String newStr) throws IOException{
		//�ȶ�ȡԭ���ļ�����,Ȼ�����д�����
		String filein = newStr + ".csv"+"\r\n";
		RandomAccessFile mm = null;
		try {
			mm = new RandomAccessFile(OK_PATH, "rw");
			mm.writeBytes(filein);
		} catch (IOException e1) {
			e1.printStackTrace();
		} finally {
			if (mm != null) {
				try {
					mm.close();
				} catch (IOException e2) {
					e2.printStackTrace();
				}
			}
		}
	}

	 public static void his() throws SQLException, IOException {
	    	logger.info("start export seven_users data!");
	    	int count = 0;
	    	File filePath = new File(File_PATH);
			File file = new File(File_PATH+File_NAME);
			if ( !filePath.exists()) {
				new File(File_PATH).mkdirs();
				logger.info("create File PATH sucess");
			}
			if (file.exists()) {
				file.delete();
			}
			file.createNewFile();
			logger.info("create New File sucess ");
	    	FileOutputStream fos = new FileOutputStream(file);
			OutputStreamWriter osw = new OutputStreamWriter(fos, "UTF-8");
			BufferedWriter bw = new BufferedWriter(osw);
			String sql =  "select /*+parallel(16)*/ t.phone_number,t.mgr_id from sended_users t join sms_template_attr a on t.mgr_id =a.id where send_time>sysdate-180 and  ((sysdate -send_time)<a.local_date and t.is_local=0) "
					+ " or ((sysdate -send_time)<a.nonlocal_date and t.is_local=1) ";
			Statement stmt = null;
			ResultSet rs = null;
			Connection conn = ConnectionManager.getInstance().getConnection();
			stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
						ResultSet.CONCUR_READ_ONLY);
			stmt.setFetchSize(500);
//				stmt.setFetchDirection(ResultSet.FETCH_REVERSE);
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				String msisdn = rs.getString("phone_number");
				try{
					String mgrId = rs.getString("mgr_id");

					if (null != msisdn && !"".equals(msisdn)) {
						String noPwdMsisdn = AES.decrypt(msisdn, "HuaDongJieSuo123");
						bw.write(noPwdMsisdn+","+mgrId+"\r\n");
					}
				} catch (Exception e) {
					logger.error("get msisdn error,msisdn={"+msisdn+"}",e);
				}
				count++;
				if (count%5000==0) {
					logger.info("already export data count "+count);
				}
			}

			if (null != bw) {
				bw.close();
			}
			if (null != osw) {
				osw.close();
			}
			if (null != fos) {
				fos.close();
			}
			if (null != rs) {
				rs.close();
			}

			if (null != stmt) {
				stmt.close();
			}
			DbUtils.closeQuietly(null, null, conn);

			logger.info("export seven_users data end! export data count "+count);
	    }

}
